package com.application.chiettinhdienluc.db;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;

import com.application.chiettinhdienluc.app.MainApplication;
import com.application.chiettinhdienluc.model.NhanCongModel;

public class NhanCongDataSource {

	private MySQLiteHelper dbHelper;
	private final String tableName = "D_NHANCONG";

	// table column names
	private static final String MA_CVIEC = "MA_CVIEC";
	private static final String TEN_CVIEC = "TEN_CVIEC";
	private static final String QUY_CACH = "QUY_CACH";
	private static final String DON_GIA = "DON_GIA";
	private static final String KHU_VUC = "KHU_VUC";
	private static final String MA_DINHMUC = "MA_DINHMUC";
	private static final String SU_DUNG = "SU_DUNG";
	private static final String NGAY_CAP_NHAT = "NGAY_CAP_NHAT";

	private final static String TAG = NhanCongDataSource.class.getSimpleName();
	

	public NhanCongDataSource(Context context) {
		dbHelper = MySQLiteHelper.getInstance();
	}

	public boolean checkNhanCongExist(String maCongViec, String maKhuVuc) {
		
		boolean existed = false;
		Cursor c = null;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				c = dbHelper.getDb().rawQuery("select rowid from "
						+ tableName + " where MA_CVIEC = " 
						+ "'" + maCongViec  + "'" + " and " + "KHU_VUC = '" + maKhuVuc + "'" , null);		
				
				if (c != null && c.getCount() > 0){
					existed = true;			
				}
			}
		} catch (Exception e) {
		}
		finally{
			if(c!=null)
			{
				c.close();
				c = null;
			}
		}
		
		return existed;	
	}
	

	public int updateStatus(NhanCongModel nhancong) {

		int numRowEffect = 0;
		
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				ContentValues cv = new ContentValues();
				
			    String suDung;
				
				if (nhancong.isSuDung()) {
					suDung = "1";
				}
				else {
					suDung = "0";
				}		

				cv.put(SU_DUNG, suDung);

				String whereClause = "MA_CVIEC" + "=?" + " and " + " KHU_VUC = ?"  ;
				String[] whereArgs = new String[] { nhancong.getMaCongViec(), nhancong.getKhuVuc()};

				numRowEffect = dbHelper.getDb().update(tableName, cv, whereClause, whereArgs);
			}
		} catch (Exception e) {
		}
		finally{
		}
		
		return numRowEffect;
	}
	
	public int update(NhanCongModel nc) {

		int numRowEffect = 0;
		
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				ContentValues cv = new ContentValues();	
				
			    cv.put(MA_CVIEC, nc.getMaCongViec());
				cv.put(KHU_VUC, nc.getKhuVuc());
				cv.put(TEN_CVIEC,nc.getTenCongViec());
				cv.put(DON_GIA, nc.getDonGia());
				cv.put(QUY_CACH, nc.getQuyCach());
				cv.put(MA_DINHMUC, nc.getMaDinhMuc());

				String whereClause = "MA_CVIEC" + "=?" + " and " + " KHU_VUC = ?"  ;
				String[] whereArgs = new String[] { nc.getMaCongViec(), nc.getKhuVuc()};

				numRowEffect = dbHelper.getDb().update(tableName, cv, whereClause, whereArgs);
			}	
		} catch (Exception e) {
		}
		finally{
		}
		
		return numRowEffect;
	}
	
	

	public long insert(NhanCongModel nc) {

		long row_id = -1;
		
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				ContentValues cv = new ContentValues();
				
			    cv.put(MA_CVIEC, nc.getMaCongViec());
				cv.put(KHU_VUC, nc.getKhuVuc());
				cv.put(TEN_CVIEC,nc.getTenCongViec());
				cv.put(DON_GIA, nc.getDonGia());
				cv.put(QUY_CACH, nc.getQuyCach());
				cv.put(MA_DINHMUC, nc.getMaDinhMuc());
						
				row_id= dbHelper.getDb().insert(tableName, null, cv);
			}
		} catch (Exception e) {
		}
		finally{
		}
		
		return row_id;	
	}


	public ArrayList<NhanCongModel> loadNhanCong() {
		ArrayList<NhanCongModel> listnhancongModel = new ArrayList<NhanCongModel>();
		Cursor c = null;
		
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				c = dbHelper.getDb().rawQuery("select * from " + NhanCongModel.NHANCONG_TABLE_NAME + " where KHU_VUC = '" + MainApplication.getMaKhuVuc() + "'"+" ORDER BY " +NhanCongModel.FIELD_TEN_NHANCONG+","+NhanCongModel.FIELD_TEN_NHANCONG +" ASC", null);

				if (c != null && c.getCount() > 0) {
					int colId_MaCongViec = c.getColumnIndex(NhanCongModel.NHANCONG_FULL_PROJECTION[0]);
					int colId_TenCongViec= c.getColumnIndex(NhanCongModel.NHANCONG_FULL_PROJECTION[1]);
					int colId_QuyCach = c.getColumnIndex(NhanCongModel.NHANCONG_FULL_PROJECTION[2]);
					int colId_DonGia = c.getColumnIndex(NhanCongModel.NHANCONG_FULL_PROJECTION[3]);
					int colId_KhuVuc = c.getColumnIndex(NhanCongModel.NHANCONG_FULL_PROJECTION[4]);
					int colId_Ma_DinhMuc = c.getColumnIndex(NhanCongModel.NHANCONG_FULL_PROJECTION[5]);
					int colId_SuDung = c.getColumnIndex(NhanCongModel.NHANCONG_FULL_PROJECTION[6]);
					int colId_NgayCapNhat = c.getColumnIndex(NhanCongModel.NHANCONG_FULL_PROJECTION[7]);

					c.moveToFirst();
					do {
						try {
							String maCongViec = c.getString(colId_MaCongViec);
							String tenCongViec = c.getString(colId_TenCongViec);
							String quyCach = c.getString(colId_QuyCach);
							double donGia = c.getDouble(colId_DonGia);
							String khuVuc = c.getString(colId_KhuVuc);
							String maDinhMuc = c.getString(colId_Ma_DinhMuc);
							int suDung = c.getInt(colId_SuDung);
							String ngayCapNhat = c.getString(colId_NgayCapNhat);

							NhanCongModel nc = new NhanCongModel();

							nc.setMaCongViec(maCongViec);
							nc.setTenCongViec(tenCongViec);
							nc.setQuyCach(quyCach);
							nc.setDonGia(donGia);
							nc.setKhuVuc(khuVuc);
							nc.setMaDinhMuc(maDinhMuc);
							nc.setSuDung(suDung == 1);
							nc.setNgayCapNhat(ngayCapNhat);
							nc.setHasChanged(false);

							listnhancongModel.add(nc);
						} catch (Exception e) {
						}
					} while (c.moveToNext());
				}
			}
		} catch (Exception e) {
			// TODO: handle exception
		}
		finally{
			if(c!=null)
			{
				c.close();
				c = null;
			}
		}

		return listnhancongModel;
	}

}